﻿-- =============================================
-- Author:		Taylor Chase and Jon Stocksdale
-- Create date: March 1 2016
-- Description:	Takes a RafikiNoSQL string and converts it to a standard 
--				SQL string that can be executed.  Uses [] and {} to determine 
--				replacement locations.  Can receive up to 4 parameters that will
--				be placed within the text of the SQL.
--				Returns "Error: Description of problem" if there is an error.
-- =============================================
CREATE FUNCTION [dbo].[ConvertRafikiNOSQL] 
(
	@RafikiNoSQL nvarchar(max),
	@JSONParameters nvarchar(MAX) = '',
	@SD datetime = NULL,
	@ED datetime = NULL,
	@TD nvarchar(100) = '' -- SHORT NAME for a TIMEDIV or CURRENT.timedivtype, PREV.timedivtype, or NEXT.timedivtype

)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @ToReplace nvarchar(max)
	DECLARE @ViewName nvarchar(max)
	DECLARE @AliasName nvarchar(max)
	DECLARE @WhereClause nvarchar(max)
	DECLARE @QueryString nvarchar(max)
	DECLARE @SystemName nvarchar(max)
	DECLARE @TempEID nvarchar(100)
	DECLARE @Reversed bit = 0
	DECLARE @E1ParameterName nvarchar(MAX)
	DECLARE @E1ParameterValue nvarchar(MAX)

	DECLARE @ViewTracker TABLE (ViewName NVARCHAR(500))

	-- Place time division into StartDate and EndDate
	SET @SD=COALESCE(dbo.GetTimeDivDate(@TD,'START',0,0),@SD)
	SET @ED=COALESCE(dbo.GetTimeDivDate(@TD,'END',0,0),@ED)

	-- Parse the JSON parameters into a table
	DECLARE @JSONTable Hierarchy
	IF @JSONParameters <> '' BEGIN
		INSERT INTO @JSONTable
		--SELECT * FROM parseJSON('{"@P1":"Hi","@P2":"Hi2"}')
		SELECT * FROM parseJSON(@JSONParameters)
	END

	WHILE CHARINDEX('[',@RafikiNoSQL) <> 0 OR CHARINDEX('{',@RafikiNoSQL) <> 0 BEGIN
		-- Set all of these values to null to prevent values from carrying over from one cycle to the next
		SET @ToReplace = NULL
		SET @ViewName = NULL
		SET @WhereClause = NULL
		SET @QueryString = NULL
		SET @SystemName = NULL
		-- @ToReplace becomes the variable string to be replaced.
		IF CHARINDEX('[',@RafikiNoSQL) <> 0 AND CHARINDEX(']',@RafikiNoSQL) <> 0 
			-- Split out the whole string that should be replaced later.
			SET @ToReplace = RTRIM(LTRIM(SUBSTRING(@RafikiNoSQL,CHARINDEX('[',@RafikiNoSQL),
													CHARINDEX(']',@RafikiNoSQL)-CHARINDEX('[',@RafikiNoSQL)+1)))
		ELSE IF CHARINDEX('{',@RafikiNoSQL) <> 0 AND CHARINDEX('}',@RafikiNoSQL) <> 0 
			-- {} may be nested inside [], but [] will not be nested inside {}
			SET @ToReplace = RTRIM(LTRIM(SUBSTRING(@RafikiNoSQL,CHARINDEX('{',@RafikiNoSQL),
													CHARINDEX('}',@RafikiNoSQL)-CHARINDEX('{',@RafikiNoSQL)+1)))
		ELSE 
			RETURN 'Error: The query is missing an ending ] or }.'  -- If the loop gets here, it needs to crash with an error.

		-- Fill the @ViewName
		IF CHARINDEX('{',@ToReplace) <> 0 AND CHARINDEX('}',@ToReplace) <> 0
			SET @ViewName = RTRIM(LTRIM(SUBSTRING(@ToReplace,CHARINDEX('{',@ToReplace)+1,
													CHARINDEX('}',@ToReplace)-CHARINDEX('{',@ToReplace)-1)))
		ELSE 
			RETURN 'Error: The query is missing an ending } or there is no view specified to be replaced.'  + @ToReplace

		-- Fill the @WhereClause, if there is an ending bracket (this means a where clause may exist)
		IF CHARINDEX(']',@ToReplace) <> 0
			SET @WhereClause = RTRIM(LTRIM(SUBSTRING(@ToReplace,CHARINDEX('}',@ToReplace)+1,LEN(@ToReplace)-CHARINDEX('}',@ToReplace)-1)))

		-- Set @AliasName
		IF CHARINDEX(' AS ',@ViewName) <> 0 BEGIN
				SET @AliasName = RTRIM(LTRIM(SUBSTRING(@Viewname,CHARINDEX(' AS ',@ViewName)+4,LEN(@ViewName))))
				SET @ViewName = RTRIM(LTRIM(SUBSTRING(@Viewname,1,CHARINDEX(' AS ',@ViewName)-1)))
		END ELSE IF CHARINDEX(' ',@ViewName) <> 0 BEGIN
				SET @AliasName = RTRIM(LTRIM(SUBSTRING(@Viewname,CHARINDEX(' ',@ViewName)+1,LEN(@ViewName))))
				SET @ViewName = RTRIM(LTRIM(SUBSTRING(@Viewname,1,CHARINDEX(' ',@ViewName)-1)))
		END ELSE IF CHARINDEX('.',@ViewName) <> 0 BEGIN
				SET @AliasName = RTRIM(LTRIM(SUBSTRING(@Viewname,CHARINDEX('.',@ViewName)+1,LEN(@ViewName))))
		END ELSE
			-- No space or period means the alias is the same as the view name
			SET @AliasName = @ViewName

		-- At this point @ViewName and @ToReplace have been specified.  If there is a @WhereClause it has also been specified.

		-- Add the view to @ViewTracker for optional parameter processing.
		INSERT INTO @ViewTracker(ViewName)
		VALUES(@ViewName)
		-- Might be needed later for optional relationship parameters?
		--IF CHARINDEX('.',@ViewName) <> 0
		--	INSERT INTO @ViewTracker(ViewName)
		--	VALUES(@ViewName)

		SET @E1ParameterValue = NULL
		SET @E1ParameterName = NULL
		-- See if the view is part of DefinedExternalViews. 
		SELECT @QueryString = SqlRaw,@SystemName = SystemName,@E1ParameterName = E1ParameterName FROM DefinedExternalViews WHERE ViewName = @ViewName
		-- If it is @QueryString will not be null and the replacement will continue
		IF NOT @QueryString IS NULL BEGIN
			-- Replace ' with '', so the query will execute properly.
			SET @QueryString = REPLACE(@QueryString,'''','''''')
			-- Replace " with '', so the internal query will execute properly.
			SET @WhereClause = REPLACE(@WhereClause,'"','''''')			
			-- Find the value of E1ParameterName
			SELECT @E1ParameterValue = StringValue FROM @JSONTable WHERE @E1ParameterName = Name AND NOT @E1ParameterName IS NULL
			IF @E1ParameterValue IS NULL 
				SELECT @E1ParameterValue = StringValue FROM @JSONTable WHERE '@GenericEntityID' = Name				
			-- If it is null, the user is not requesting Oracle-Side filtering, so pass the null to the 
			-- The replacement code is complicated enough to move outside of ConvertSQL
			-- This will return a "wrapped" command that includes the OpenQuery command.
			SET @QueryString = dbo.ConvertRafikiNOSQL_ExternalSQL(@QueryString,@ViewName,@SystemName,@AliasName,@WhereClause,@E1ParameterValue)
			IF LEFT(@QueryString,7) = 'Error: '
				RETURN @QueryString
			-- Replace @ToReplace with OpenQuery command.
			SET @RafikiNoSQL = REPLACE (@RafikiNoSQL,@ToReplace,@QueryString)
		END ELSE BEGIN
			-- See if the query is part of DefinedExecutableCommands
			SELECT @QueryString = RafikiNoSQL FROM DefinedExecutableCommands WHERE  ExecName = @ViewName
			IF NOT @QueryString IS NULL BEGIN
				-- Replace ' with '', so the query will execute properly.
				SET @QueryString = REPLACE(@QueryString,'''','''''')
				SET @WhereClause = REPLACE(@WhereClause,'''','''''')
				-- Replace @ToReplace with the actual NOSQL executable code
				SET @RafikiNoSQL = REPLACE (@RafikiNoSQL,@ToReplace, @QueryString)
			END ELSE BEGIN
				-- See if the view is part of DefinedViews.
				SELECT @QueryString = RafikiNoSQL FROM DefinedViews WHERE ViewName = @ViewName
				-- See if the view is part of DefinedEntityTypes where there is a RafikiNOSQL value specified. 
				SELECT @QueryString = RafikiNoSQL FROM DefinedEntityTypes WHERE EntityType = @ViewName AND NOT RafikiNOSQL IS NULL
				-- Replace ' with '', so the query will execute properly.
				SET @QueryString = REPLACE(@QueryString,'''','''''')
				SET @WhereClause = REPLACE(@WhereClause,'''','''''')
				IF @QueryString IS NULL BEGIN
					-- Maybe the braces are referring to a non-external entitytype or any shortrelationshipname
					SET @QueryString = dbo.ConvertRafikiNOSQL_EntityOrRelSQL(@ViewName)
					IF @QueryString IS NULL OR LEFT(@QueryString,6) = 'Error:'
						RETURN COALESCE(@QueryString,'Error: The view/relationship/entity name ' + @ViewName + ' does not exist in DefinedExternalViews, DefinedEntityRelationships, DefinedEntityTypes or DefinedViews.')
					--ELSE BEGIN
					--	-- if it is a reverse relationship, swap E1 and E2 (the first time!)
					--	IF LEFT(@QueryString,9) = '(REVERSE;' BEGIN
					--		SET @QueryString = '(' + SUBSTRING(@QueryString,10,len(@QueryString))
					--	END
					--END
				END
				-- Replace @ToReplace with the actual query.
				SET @RafikiNoSQL = REPLACE (@RafikiNoSQL,@ToReplace, '(SELECT * FROM (' + @QueryString + ') AS ' + @AliasName + ' ' + COALESCE(@WhereClause,'') + ') AS ' + @AliasName )
			END
		END
	END

	-- Replace " with '', so the query will execute properly.
	SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'"','''')

	-- The last step before returning the command is to insert the parameters
	-- Find all @@ occurences and replace them with a nonsense string:
	SET @RafikiNoSQL = REPLACE(@RafikiNOSQL,'@@','#ThisisanonsensestringoftextthatshouldnotevershowupAABBCCDD0987!#')

	-- Loop through the JSON parameters.
	DECLARE @ReplaceCursor CURSOR
	DECLARE @ParamName nvarchar(100),@Stringvalue nvarchar(4000)
	SET @ReplaceCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT Name,StringValue FROM @JSONTable WHERE [Object_ID] IS NULL
		
	OPEN @ReplaceCursor
	FETCH NEXT FROM @ReplaceCursor
	INTO @ParamName,@StringValue
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,@ParamName,@Stringvalue)
		-- If there are fresh @ symbols make sure to remove the @@ ones.
		SET @RafikiNoSQL = REPLACE(@RafikiNOSQL,'@@','#ThisisanonsensestringoftextthatshouldnotevershowupAABBCCDD0987!#')

		FETCH NEXT FROM @ReplaceCursor
		INTO @ParamName,@StringValue
	END
	CLOSE @ReplaceCursor
	DEALLOCATE @ReplaceCursor
	
	-- Replace the date and timediv parameters
	SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'@TD',@TD)
	IF NOT @SD IS NULL
		SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'@SD', CONVERT(nvarchar,@SD,101))
	ELSE
		SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'@SD', '' )
	IF NOT @ED IS NULL
		SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'@ED', CONVERT(nvarchar,@ED,101))
	ELSE
		IF NOT @SD IS NULL
			SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'@ED', CONVERT(nvarchar,@SD,101))
		ELSE
			SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,'@ED', '' )

	-- Find and remove all specified optional parameters (replacing them with '')
	DECLARE @OptionalParamsCursor CURSOR
	SET @OptionalParamsCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT DISTINCT ParameterName From DefinedViewParameters DVP INNER JOIN @ViewTracker VT ON DVP.ViewName = VT.ViewName
		
	OPEN @OptionalParamsCursor
	FETCH NEXT FROM @OptionalParamsCursor
	INTO @ParamName
	WHILE @@FETCH_STATUS = 0
	BEGIN

		SET @RafikiNoSQL = REPLACE(@RafikiNoSQL,@ParamName,'')
		FETCH NEXT FROM @OptionalParamsCursor
		INTO @ParamName
	END
	CLOSE @OptionalParamsCursor
	DEALLOCATE @OptionalParamsCursor



	IF CHARINDEX('@',@RafikiNoSQL) <> 0
		RETURN 'Error: There are parameters specified in the query that are not specified in the input.' + @RafikiNOSQL

	-- Return the @@ to the code, but remove one @ because this is a safety for email addresses.
	SET @RafikiNoSQL = REPLACE(@RafikiNOSQL,'#ThisisanonsensestringoftextthatshouldnotevershowupAABBCCDD0987!#','@')


	RETURN @RafikiNoSQL
END